<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;

class Store extends Model
{
    use HasFactory;

    protected $table = 'store';

    protected $primaryKey = 'id';

    public $timestamps = false;

    public function getList(array $params): array
    {
        $whereSql = self::conditions($params);

        $sql = "
            SELECT
                s.id,
                s.name,
                s.status,
                s.city_id,
                acc.area_name as city_name,
                s.description,
                s.pathid,
                s.address,
                s.phone,
                s.lon,
                s.lat,
                s.remark,
                ad.area_name as area_district_name,
                ad.pathid as area_district_pathid,
                ac.area_name as area_city_name,
                ac.pathid as area_city_pathid,
                ap.area_name as area_province_name,
                ap.pathid as area_province_pathid,
                0 as distance,
                s.online_flg,
                s.delete_flg,
                s.create_at,
                s.update_at
            FROM
                store s
                LEFT JOIN area ad ON ad.pathid = s.pathid
                LEFT JOIN area ac ON ac.id = ad.parent_id
                LEFT JOIN area ap ON ap.id = ac.parent_id
                LEFT JOIN area acc ON acc.pathid = s.city_id
            WHERE
                s.delete_flg = 0
                {$whereSql}
            ORDER BY
                distance ASC
        ";
        return DB::select($sql);
    }

    public function cityStore(array $params): array
    {
        $whereSql = self::conditions($params);

        $sql = "
            SELECT
                s.city_id,
                acc.area_name as city_name,
                count(s.id) as count
            FROM
                store s
                LEFT JOIN area acc ON acc.pathid = s.city_id
            WHERE
                s.delete_flg = 0
                {$whereSql}
            GROUP BY
                s.city_id
        ";
        return DB::select($sql);
    }

    public static function conditions(array $params): string
    {
        $whereSql = "";

        if (!empty($params['status'])) {
            $whereSql .= "and s.status = '{$params['status']}'";
        }
        if (!empty($params['city_id'])) {
            $whereSql .= "and s.city_id = '{$params['city_id']}'";
        }
        if (!empty($params['online_flg'])) {
            $whereSql .= "and s.online_flg = '{$params['online_flg']}'";
        }
        if (!empty($params['id'])) {
            $whereSql .= "and s.id = '{$params['id']}'";
        }

        return $whereSql;
    }
}
